﻿'========================================================================
'功能:公佈欄DB物件
'作者:andy
'日期: 2011/9/10
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data


Namespace WorkFlow.DAO
    Public Class WFBillBoardDAO
        Inherits OAMS.DAO.BaseDAO

        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub
        Public Function GetDataBillNo(ByVal BillNo As Integer) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@BillNo", SqlDbType.Int)
            sqlParams(0).Value = BillNo
            Dim sb As New StringBuilder()
            sb.AppendLine(" select A.* ,B.emp_name as CrtName,C.emp_name  as ModiName,D.GroupName as TargetGroupName, E.GroupName as CrtGroupName ")
            sb.AppendLine(" from  WF_BillBoard A    ")
            sb.AppendLine("Left join Users B on A.CrtUID=B.emp_no ")
            sb.AppendLine("Left join Users C on A.CrtUID=C.emp_no ")
            sb.AppendLine("Left join GroupID D on A.TargetGroupID=D.GroupID ")
            sb.AppendLine("Left join GroupID E on A.CrtGroupID=E.GroupID ")
            sb.AppendLine("where BillNo=@BillNo  order by OrderBy,BillNo")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

        Public Function GetDataUIDEdit(ByVal UID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID
            Dim sb As New StringBuilder()
            sb.AppendLine(" select A.* ,B.emp_name as CrtName,C.emp_name  as ModiName,D.GroupName as TargetGroupName, E.GroupName as CrtGroupName ")
            sb.AppendLine(" from  WF_BillBoard A    ")
            sb.AppendLine("Left join Users B on A.CrtUID=B.emp_no ")
            sb.AppendLine("Left join Users C on A.CrtUID=C.emp_no ")
            sb.AppendLine("Left join GroupID D on A.TargetGroupID=D.GroupID ")
            sb.AppendLine("Left join GroupID E on A.CrtGroupID=E.GroupID ")
            sb.AppendLine("where CrtGroupID in (select  GroupID  from GroupMember  where UID=@UID)   order by OrderBy,BillNo")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function
        Public Sub InsertBillBoard(ByVal Subject As String, ByVal URL As String, ByVal TargetGroupID As String, ByVal CrtUID As String, ByVal CrtGroupID As String, ByVal BegDate As DateTime, ByVal EndDate As DateTime, ByVal Active As Boolean, ByVal OrderBy As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(8) {}
            sqlParams(0) = New SqlParameter("@Subject", SqlDbType.NVarChar)
            sqlParams(0).Value = Subject
            sqlParams(1) = New SqlParameter("@URL", SqlDbType.NVarChar)
            sqlParams(1).Value = URL
            sqlParams(2) = New SqlParameter("@TargetGroupID", SqlDbType.NVarChar)
            sqlParams(2).Value = TargetGroupID
            sqlParams(3) = New SqlParameter("@CrtUID", SqlDbType.NVarChar)
            sqlParams(3).Value = CrtUID
            sqlParams(4) = New SqlParameter("@CrtGroupID", SqlDbType.NVarChar)
            sqlParams(4).Value = CrtGroupID
            sqlParams(5) = New SqlParameter("@BegDate", SqlDbType.DateTime)
            sqlParams(5).Value = BegDate
            sqlParams(6) = New SqlParameter("@EndDate", SqlDbType.DateTime)
            sqlParams(6).Value = EndDate
            sqlParams(7) = New SqlParameter("@Active", SqlDbType.Bit)
            sqlParams(7).Value = Active
            sqlParams(8) = New SqlParameter("@OrderBy", SqlDbType.VarChar, 20)
            sqlParams(8).Value = OrderBy
            Dim sb As New StringBuilder()
            sb.AppendLine(" insert into WF_BillBoard(   Subject, URL, TargetGroupID, CrtUID, CrtDate, ModiUID, ModiDate, CrtGroupID, BegDate, EndDate,Active,OrderBy) values(@Subject, @URL, @TargetGroupID, @CrtUID, getdate(),'', null, @CrtGroupID, @BegDate, @EndDate,@Active,@OrderBy)")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub

        Public Sub UpdateBillBoard(ByVal BillNo As Integer, ByVal Subject As String, ByVal URL As String, ByVal TargetGroupID As String, ByVal CrtUID As String, ByVal CrtGroupID As String, ByVal BegDate As DateTime, ByVal EndDate As DateTime, ByVal Active As Boolean, ByVal OrderBy As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(9) {}
            sqlParams(0) = New SqlParameter("@Subject", SqlDbType.NVarChar)
            sqlParams(0).Value = Subject
            sqlParams(1) = New SqlParameter("@URL", SqlDbType.NVarChar)
            sqlParams(1).Value = URL
            sqlParams(2) = New SqlParameter("@TargetGroupID", SqlDbType.NVarChar)
            sqlParams(2).Value = TargetGroupID
            sqlParams(3) = New SqlParameter("@CrtUID", SqlDbType.NVarChar)
            sqlParams(3).Value = CrtUID
            sqlParams(4) = New SqlParameter("@CrtGroupID", SqlDbType.NVarChar)
            sqlParams(4).Value = CrtGroupID
            sqlParams(5) = New SqlParameter("@BegDate", SqlDbType.DateTime)
            sqlParams(5).Value = BegDate
            sqlParams(6) = New SqlParameter("@EndDate", SqlDbType.DateTime)
            sqlParams(6).Value = EndDate
            sqlParams(7) = New SqlParameter("@Active", SqlDbType.Bit)
            sqlParams(7).Value = Active
            sqlParams(8) = New SqlParameter("@BillNo", SqlDbType.Int)
            sqlParams(8).Value = BillNo
            sqlParams(9) = New SqlParameter("@OrderBy", SqlDbType.VarChar, 20)
            sqlParams(9).Value = OrderBy
            Dim sb As New StringBuilder()
            sb.AppendLine(" update WF_BillBoard set    Subject=@Subject, URL=@URL, TargetGroupID=@TargetGroupID,  ModiUID=@CrtUID, ModiDate=GetDate(), CrtGroupID=@CrtGroupID, BegDate=@BegDate, EndDate =@EndDate ,Active=@Active ,OrderBy=@OrderBy where BillNo =@BillNo ")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub

        Public Function GetDataUID(ByVal UID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID
            Dim sb As New StringBuilder()
            sb.AppendLine(" select * ")
            sb.AppendLine(" from  WF_BillBoard     ")
            sb.AppendLine("where (TargetGroupID=''  or TargetGroupID in (select GroupID  from GroupMember where UID=@UID)) and Active=1 order by   OrderBy ,BillNo")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function


    End Class


End Namespace


